package com.yang.dao;

import com.yang.entity.Address;
import com.yang.entity.Product;
import com.yang.entity.Productcar;

import java.sql.*;
import java.util.*;

public class ProductcarDao {
    //根据sql查询
    public List<Productcar> queryProductcarsBySql(String sql) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List<Productcar> list = new ArrayList<>();
            while (rs.next()) {
                Productcar obj = new Productcar();
                obj.setId(rs.getInt("id"));
                obj.setPid(rs.getInt("pid"));
                obj.setName(rs.getString("name"));
                obj.setUrl(rs.getString("url"));
                obj.setPrice(rs.getDouble("price"));
                obj.setNum(rs.getInt("num"));
                obj.setDetail(rs.getString("detail"));
                obj.setCategoryid(rs.getInt("categoryid"));
                obj.setUid(rs.getInt("uid"));
                list.add(obj);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return null;
    }
    //根据sql查询
    public List<Product> queryProductcarBySql(String sql) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List<Product> list = new ArrayList<>();
            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setUrl(rs.getString("url"));
                product.setPrice(rs.getDouble("price"));
                product.setStock(rs.getInt("stock"));
                product.setDetail(rs.getString("detail"));
                product.setCategoryid(rs.getInt("categoryid"));
                list.add(product);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return null;
    }
    //查询购物车是否存在商品
    public Productcar queryProductcarPidUid(Integer pid,Integer uid) {
        Connection conn=BaseDao.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps = conn.prepareStatement("select * from Productcar where pid=? and uid=?");
            ps.setInt(1,pid);
            ps.setInt(2,uid);
            rs = ps.executeQuery();
            if (rs.next()) {
                Productcar obj = new Productcar();
                obj.setId(rs.getInt("id"));
                obj.setPid(rs.getInt("pid"));
                obj.setName(rs.getString("name"));
                obj.setUrl(rs.getString("url"));
                obj.setPrice(rs.getDouble("price"));
                obj.setNum(rs.getInt("num"));
                obj.setDetail(rs.getString("detail"));
                obj.setCategoryid(rs.getInt("categoryid"));
                obj.setUid(rs.getInt("uid"));
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return null;
    }

    //根据id修改购买的数量
    public int updateProductCartNumber(int id,int num) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("update Productcar set num=? where id=?");
            ps.setInt(1, num);
            ps.setInt(2,id);
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }
    public int saveProductcar(Productcar obj) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("insert into productcar(name, url, price, uid, detail, categoryId,pid,num) values (?, ?, ?, ?, ?, ?,?,?)");
            ps.setString(1, obj.getName());
            ps.setString(2, obj.getUrl());
            ps.setDouble(3, obj.getPrice());
            ps.setInt(4, obj.getUid());
            ps.setString(5, obj.getDetail());
            ps.setInt(6, obj.getCategoryid());
            ps.setInt(7, obj.getPid());
            ps.setInt(8, obj.getNum());
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, ps, conn);
        }
        return 0;
    }
    //根据用户id查询购物车商品
    public List<Productcar> queryByUid(Integer uid) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List <Productcar> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement("select * from Productcar where uid=?");
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Productcar productcar = new Productcar();
                productcar.setId(rs.getInt("id"));
                productcar.setName(rs.getString("name"));
                productcar.setUrl(rs.getString("url"));
                productcar.setPrice(rs.getDouble("price"));
                productcar.setNum(rs.getInt("num"));
                productcar.setDetail(rs.getString("detail"));
                productcar.setCategoryid(rs.getInt("categoryid"));
                productcar.setUid(rs.getInt("uid"));
                list.add(productcar);
            }
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, ps, conn);
        }
        return list;
    }
    //删除购物车商品
    public int deleteProductcar(Integer id) {
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        int sum=0;
        try {
            ps=conn.prepareStatement("delete from Productcar where id=?");
            ps.setInt(1,id);
             sum = ps.executeUpdate();
            return sum;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            BaseDao.close(null, ps, conn);
        }
        return sum;
    }

    //下订单
    public int goBuyShopping(List<Productcar> cars, Address addr){
        Connection conn=BaseDao.getConn();
        PreparedStatement ps=null;
        //计算总金额
        double sumPrice=0;
        try {
            //关闭自动提交
            conn.setAutoCommit(false);
            //修改库存
            ps=conn.prepareStatement("update product set stock=stock-? where id=?");
            for (Productcar car : cars) {
                //计算总金额
                sumPrice=sumPrice+car.getPrice()*car.getNum();
                ps.setInt(1,car.getNum());
                ps.setInt(2,car.getPid());
                ps.addBatch();
            }
            ps.executeBatch();
            //产生订单编号
            String orderId=UUID.randomUUID().toString();
            //添加订单
            ps=conn.prepareStatement("insert into orders(id,username,address,creationTime,tel,totalAmount,status,userId) values(?,?,?,now(),?,?,?,?)");
            ps.setString(1,orderId);
            ps.setString(2,addr.getContacts());
            ps.setString(3,addr.getAddress());
            ps.setString(4,addr.getTel());
            ps.setDouble(5,sumPrice);
            ps.setInt(6,1);//下单状态
            ps.setInt(7,addr.getUserid());
            if(ps.executeUpdate()==0){
                conn.rollback();//回滚前面所有的操作
            }
            //添加订单详情
            ps=conn.prepareStatement("insert into orderdetail(productName,productCrice,productCount,orderId,productId) values(?,?,?,?,?)");
            for (Productcar car : cars) {
                ps.setString(1,car.getName());
                ps.setDouble(2,car.getPrice());
                ps.setInt(3,car.getNum());
                ps.setString(4,orderId);
                ps.setInt(5,car.getPid());
                ps.addBatch();
            }
            ps.executeBatch();
            //删除购物车表
            ps=conn.prepareStatement("delete from productcar where id=?");
            for (Productcar car : cars) {
                ps.setInt(1,car.getId());
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();//上面没报错，提交！！！
            return 1;
        }catch (Exception e){
            try {
                //报错直接回滚
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            BaseDao.close(null,ps,conn);
        }
        return 0;
    }
}
